#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class RoomInfoProvider : CY.GFive.Core.Providers.DALProvider.IRoomInfoProvider
	{
		#region Sql
		private static readonly string SqlSelectRoomInfo = "SELECT [Id], [RoomCode],[BuildingCode],[RoomNum],[TotalNum],[ExistNum],[AvailNum],[RoomState] FROM [RoomInfo] WHERE  [Id]=@Id";

		private static readonly string SqlInsertRoomInfo = "INSERT INTO [RoomInfo]([RoomCode],[BuildingCode],[RoomNum],[TotalNum],[ExistNum],[AvailNum],[RoomState]) values(@RoomCode,@BuildingCode,@RoomNum,@TotalNum,@ExistNum,@AvailNum,@RoomState);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateRoomInfo = "UPDATE [RoomInfo] SET [RoomCode] = @RoomCode,[BuildingCode] = @BuildingCode,[RoomNum] = @RoomNum,[TotalNum] = @TotalNum,[ExistNum] = @ExistNum,[AvailNum] = @AvailNum,[RoomState] = @RoomState WHERE  [Id]=@Id";

		private static readonly string SqlDeleteRoomInfo = "DELETE [RoomInfo] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllRoomInfo = "SELECT [Id], [RoomCode],[BuildingCode],[RoomNum],[TotalNum],[ExistNum],[AvailNum],[RoomState] FROM [RoomInfo]";
		#endregion

		#region IRoomInfoProvider Members
		public Core.Business.RoomInfo Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectRoomInfo);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.RoomInfo roomInfo = new Core.Business.RoomInfo();

				if (!reader.IsDBNull(0)) roomInfo.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) roomInfo.RoomCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) roomInfo.BuildingCode = reader.GetString(2);
				if (!reader.IsDBNull(3)) roomInfo.RoomNum = reader.GetString(3);
				if (!reader.IsDBNull(4)) roomInfo.TotalNum = reader.GetInt32(4);
				if (!reader.IsDBNull(5)) roomInfo.ExistNum = reader.GetInt32(5);
				if (!reader.IsDBNull(6)) roomInfo.AvailNum = reader.GetInt32(6);
				if (!reader.IsDBNull(7)) roomInfo.RoomState = reader.GetString(7);

				return roomInfo;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.RoomInfo roomInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@RoomCode", SqlDbType.NVarChar, roomInfo.RoomCode);
			sql.AddParameter("@BuildingCode", SqlDbType.NVarChar, roomInfo.BuildingCode);
			sql.AddParameter("@RoomNum", SqlDbType.NVarChar, roomInfo.RoomNum);
			sql.AddParameter("@TotalNum", SqlDbType.Int, roomInfo.TotalNum);
			sql.AddParameter("@ExistNum", SqlDbType.Int, roomInfo.ExistNum);
			sql.AddParameter("@AvailNum", SqlDbType.Int, roomInfo.AvailNum);
			sql.AddParameter("@RoomState", SqlDbType.NVarChar, roomInfo.RoomState);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertRoomInfo);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) roomInfo.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.RoomInfo roomInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@RoomCode", SqlDbType.NVarChar, roomInfo.RoomCode);
			sql.AddParameter("@BuildingCode", SqlDbType.NVarChar, roomInfo.BuildingCode);
			sql.AddParameter("@RoomNum", SqlDbType.NVarChar, roomInfo.RoomNum);
			sql.AddParameter("@TotalNum", SqlDbType.Int, roomInfo.TotalNum);
			sql.AddParameter("@ExistNum", SqlDbType.Int, roomInfo.ExistNum);
			sql.AddParameter("@AvailNum", SqlDbType.Int, roomInfo.AvailNum);
			sql.AddParameter("@RoomState", SqlDbType.NVarChar, roomInfo.RoomState);
			sql.AddParameter("@Id", SqlDbType.Int, roomInfo.Id);
			sql.ExecuteSql(SqlUpdateRoomInfo);
		}

		public void Delete(Core.Business.RoomInfo roomInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, roomInfo.Id);
			sql.ExecuteSql(SqlDeleteRoomInfo);
		}

		public IList<Core.Business.RoomInfo> GetAllRoomInfo()
		{
			IList<Core.Business.RoomInfo> roomInfolist = new List<Core.Business.RoomInfo>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllRoomInfo);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.RoomInfo roomInfo = new Core.Business.RoomInfo();

					if (!reader.IsDBNull(0)) roomInfo.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) roomInfo.RoomCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) roomInfo.BuildingCode = reader.GetString(2);
					if (!reader.IsDBNull(3)) roomInfo.RoomNum = reader.GetString(3);
					if (!reader.IsDBNull(4)) roomInfo.TotalNum = reader.GetInt32(4);
					if (!reader.IsDBNull(5)) roomInfo.ExistNum = reader.GetInt32(5);
					if (!reader.IsDBNull(6)) roomInfo.AvailNum = reader.GetInt32(6);
					if (!reader.IsDBNull(7)) roomInfo.RoomState = reader.GetString(7);

					roomInfo.MarkOld();
					roomInfolist.Add(roomInfo);
				}
				reader.Close();
			}
			return roomInfolist;
		}
		#endregion
	}
}
